package com.ecp.commons.db;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.ecp.commons.annotation.VariantType;
import com.ecp.commons.bean.Page;
import com.ecp.commons.cache.CacheService;
import com.ecp.commons.dao.IDSDAO;
import com.ecp.commons.serialization.SerializableObject;

@Repository("genericDAO")
public class GenericDAOImpl<T extends SerializableObject> extends JdbcDaoSupport implements GenericDAO<T> {

	private static final Log		log			= LogFactory.getLog(GenericDAOImpl.class);

	private static final boolean	LOGENABLE	= false;

	private IDSDAO					idsDAO		= null;

	@Resource
	private CacheService			entityCache;

	//private boolean					needCache	= true;

	@Resource
	public void setJDBCTemplate(JdbcTemplate jdbcTemplate) {
		super.setJdbcTemplate(jdbcTemplate);
	}

	@Resource(name = "idsDAO")
	public void setIdsDAO(IDSDAO idsDAO) {
		this.idsDAO = idsDAO;
	}

	@Override
	public int generateId(T obj) throws Exception {
		String tableName = obj.getEntity();
		return idsDAO.generateId(tableName);
	}

	@Override
	public void add(T obj) throws Exception {
		// Generate Primary Key.
		generatePK(obj);

		// Reflect to find filed and value.
		JSONObject jobj = parseEntiryAndColumn(obj);
		JSONObject jtmp;

		// Generate sql and parameter list.
		final StringBuilder insert = new StringBuilder("INSERT INTO ");
		StringBuilder values = new StringBuilder("VALUES (");
		final List<Object> paramList = new LinkedList<Object>();
		insert.append(jobj.getString("entity")).append(" (");
		for (int i = 0; i < jobj.getJSONArray("column").length(); i++) {
			jtmp = jobj.getJSONArray("column").getJSONObject(i);
			insert.append("`").append(jtmp.getString("name")).append("`,");
			values.append("?,");
			paramList.add(jtmp.get("value"));
		}
		values.deleteCharAt(values.length() - 1).append(")");
		insert.deleteCharAt(insert.length() - 1).append(") ").append(values);

		if (LOGENABLE) {
			log.info("Generic SQL [" + insert.toString() + Arrays.toString(paramList.toArray()));
		}
		try {
			execute(insert.toString(), paramList);
		} catch (Exception e) {
			if (e.getMessage().startsWith("Duplicate entry")) {
			}
			throw e;
		}
		if (obj.needCached()) {
			entityCache.putEntity(obj.getClass(), obj.getPkValue(), obj, true);
		}
	}

	@Override
	public void addBatch(List<T> list, boolean inTrans) throws Exception {
		if (list.size() == 0) {
			return;
		}

		T obj = list.get(0);
		JSONObject jobj = parseEntiryAndColumn(obj);

		// Generate sql and parameter list.
		final StringBuilder insert = new StringBuilder("INSERT INTO ");
		StringBuilder values = new StringBuilder("VALUES (");
		insert.append(jobj.getString("entity")).append(" (");
		for (int i = 0; i < jobj.getJSONArray("column").length(); i++) {
			JSONObject jtmp = jobj.getJSONArray("column").getJSONObject(i);
			insert.append("`").append(jtmp.getString("name")).append("`,");
			values.append("?,");
		}
		values.deleteCharAt(values.length() - 1).append(")");
		insert.deleteCharAt(insert.length() - 1).append(") ").append(values);

		final List<List<Object>> batchParamList = new LinkedList<List<Object>>();
		for (T bean : list) {
			generatePK(bean);
			JSONObject jobj2 = parseEntiryAndColumn(bean);
			final List<Object> paramList = new LinkedList<Object>();
			for (int i = 0; i < jobj2.getJSONArray("column").length(); i++) {
				JSONObject jtmp2 = jobj2.getJSONArray("column").getJSONObject(i);
				paramList.add(jtmp2.get("value"));
			}
			batchParamList.add(paramList);
		}

		if (LOGENABLE) {
			log.info("Generic SQL [" + insert.toString() + Arrays.toString(batchParamList.toArray()));
		}
		try {
			executeBatch(insert.toString(), batchParamList, inTrans);
		} catch (Exception e) {
			if (e.getMessage().startsWith("Duplicate entry")) {
			}
			throw e;
		}
		if (obj.needCached()) {
			for (T bean : list) {
				entityCache.putEntity(bean.getClass(), bean.getPkValue(), bean, true);
			}
		}
	}

	@Override
	public void update(T obj) throws Exception {
		JSONObject jobj = parseEntiryAndColumn(obj);
		JSONObject jtmp;

		// Generate sql and parameter list.
		StringBuilder update = new StringBuilder("UPDATE ");
		StringBuilder where = new StringBuilder("WHERE ");
		List<Object> paramList = new LinkedList<Object>();
		update.append(jobj.getString("entity")).append(" SET");
		int pkIndex = 0;
		for (int i = 0; i < jobj.getJSONArray("column").length(); i++) {
			jtmp = jobj.getJSONArray("column").getJSONObject(i);
			if (jtmp.getBoolean("pk")) {
				pkIndex = i;
				where.append("`").append(jtmp.getString("name")).append("`=?;");
			} else {
				update.append(" `").append(jtmp.getString("name")).append("`=?,");
			}
			paramList.add(jtmp.get("value"));
		}
		update.deleteCharAt(update.length() - 1).append(" ").append(where);
		paramList.add(paramList.remove(pkIndex));

		if (LOGENABLE) {
			log.info("Generic SQL [" + update.toString() + Arrays.toString(paramList.toArray()));
		}
		execute(update.toString(), paramList);
		if (obj.needCached()) {
			entityCache.updateEntity(obj.getClass(), obj);
		}
	}

	@Override
	public void delete(Serializable pk, T t) throws Exception {
		StringBuilder delete = new StringBuilder("DELETE FROM ");
		delete.append(t.getEntity()).append(" WHERE ").append(t.getPk()).append("=?;");

		if (LOGENABLE) {
			log.info("Generic SQL [" + delete.toString() + "[" + pk + "]");
		}
		execute(delete.toString(), Arrays.asList(new Object[] { pk }));
		if (t.needCached()) {
			entityCache.removeEntity(t.getClass(), pk);
		}
	}

	@Override
	public void deleteBatch(List<?> pks, T t, boolean inTrans) throws Exception {
		if (pks.size() == 0) {
			return;
		}
		StringBuilder delete = new StringBuilder("DELETE FROM ");
		delete.append(t.getEntity()).append(" WHERE ").append(t.getPk()).append("=?;");

		if (LOGENABLE) {
			log.info("Generic SQL [" + delete.toString() + "|" + pks + "]");
		}
		List<List<Object>> batchParamList = new LinkedList<List<Object>>();
		for (Object pk : pks) {
			List<Object> paramList = new LinkedList<Object>();
			paramList.add(pk);
			batchParamList.add(paramList);
		}
		executeBatch(delete.toString(), batchParamList, inTrans);
		if (t.needCached()) {
			for (Object pk : pks) {
				entityCache.removeEntity(t.getClass(), pk);
			}
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public T load(Serializable pk, T t) throws Exception {
		T instance = null;
		if (t.needCached()) {
			instance = (T) entityCache.getEntity(t.getClass(), pk);
			if (instance != null) {
				if (instance.getPkValue().equals("-1")) {
					return null;
				}
				return instance;
			}
		}
		String sql = "SELECT * FROM " + t.getEntity() + " WHERE " + t.getPk() + "=?;";
		instance = executeQuery(sql, Arrays.asList(new Object[] { pk }), t);
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql + "[" + pk + "]");
		}
		if (t.needCached()) {
			entityCache.putEntity(t.getClass(), pk, instance, false);
		}
		return instance;
	}

	@SuppressWarnings("unchecked")
	@Override
	public T getEntity(String sql, Object[] args, T t) throws Exception {
		T instance = null;
		if (t.needCached()) {
			instance = (T) entityCache.getEntity(t.getClass(), sql, args);
			if (instance != null) {
				if (instance.getPkValue().equals("-1")) {
					return null;
				}
				return instance;
			}
		}
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql + Arrays.toString(args));
		}
		instance = executeQuery(sql, Arrays.asList(args), t);
		if (t.needCached()) {
			entityCache.putEntity(t.getClass(), sql, args, instance);
		}
		return instance;
	}

	@SuppressWarnings("unchecked")
	@Override
	public T getEntity(String[] keys, Object[] args, T t) throws Exception {
		T instance = null;
		if (t.needCached()) {
			// TODO: 针对这种模式单独缓存
			//String searchString = Arrays.toString(keys) + Arrays.toString(args);
			instance = (T) entityCache.getEntity(t.getClass(), Arrays.toString(keys), args);
			if (instance != null) {
				if (instance.getPkValue().equals("-1")) {
					return null;
				}
				return instance;
			}
		}
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM ").append(t.getEntity()).append(" WHERE 1=1");
		for (String key : keys) {
			sql.append(" AND ").append(key).append(" = ?");
		}
		instance = executeQuery(sql.toString(), Arrays.asList(args), t);
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql.toString() + Arrays.toString(args));
		}
		if (t.needCached()) {
			entityCache.putEntity(t.getClass(), Arrays.toString(keys), args, instance);
		}
		return instance;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> getAll(T t) throws Exception {
		List<T> instanceList = null;
		if (t.needCached()) {
			instanceList = (List<T>) entityCache.getEntityList(t.getClass(), "", new Object[] {}, null);
			if (instanceList != null) {
				return instanceList;
			}
		}
		String sql = "SELECT * FROM " + t.getEntity();
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql);
		}
		instanceList = executeQueryList(sql, new LinkedList<Object>(), t);
		if (t.needCached()) {
			entityCache.putEntityList(t.getClass(), "", new Object[] {}, instanceList, null);
		}
		return instanceList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> getEntityList(String sql, Object[] args, T t) throws Exception {
		if (args == null) {
			args = new Object[] {};
		}
		List<T> instanceList = null;
		if (t.needCached()) {
			instanceList = (List<T>) entityCache.getEntityList(t.getClass(), sql, args, null);
			if (instanceList != null) {
				return instanceList;
			}
		}
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql + Arrays.toString(args));
		}
		instanceList = executeQueryList(sql, Arrays.asList(args), t);
		if (t.needCached()) {
			entityCache.putEntityList(t.getClass(), sql, args, instanceList, null);
		}
		return instanceList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> getEntityList(String sql, Object[] args, T t, Page page) throws Exception {
		Integer iTotal = 0;
		List<T> instanceList = null;
		if (t.needCached()) {
			instanceList = (List<T>) entityCache.getEntityList(t.getClass(), sql, args, page);
			if (instanceList != null) {
				return instanceList;
			}
		}

		List<Object> params = new LinkedList<Object>();
		params.addAll(Arrays.asList(args));
		StringBuilder tmp = new StringBuilder(sql.trim().replaceAll(";$", ""));

		if (page != null) {
			StringBuilder total = new StringBuilder("SELECT count(*) FROM ");
			total.append(sql.toUpperCase().trim().replaceAll("^SELECT.*?\\sFROM\\s", ""));
			iTotal = queryCount(total.toString(), params);
			page.setTotalCount(iTotal);

			tmp.append(" LIMIT ?,?;");
			params.add(Integer.valueOf((page.getCurrentPage() - 1) * page.getPageSize()));
			params.add(Integer.valueOf(page.getPageSize()));
		}

		if (LOGENABLE) {
			log.info("Generic SQL [" + tmp.toString() + Arrays.toString(args));
		}
		instanceList = executeQueryList(tmp.toString(), params, t);
		if (t.needCached()) {
			entityCache.putEntityList(t.getClass(), sql, args, instanceList, page);
		}
		return instanceList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> getEntityList(String[] keys, Object[] args, Page page, T t) throws Exception {
		List<T> instanceList = null;
		if (t.needCached()) {
			instanceList = (List<T>) entityCache.getEntityList(t.getClass(), Arrays.toString(keys), args, page);
			if (instanceList != null) {
				return instanceList;
			}
		}

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM ").append(t.getEntity()).append(" WHERE 1=1");
		for (String key : keys) {
			sql.append(" AND ").append(key).append(" = ?");
		}
		List<Object> params = new LinkedList<Object>();
		params.addAll(Arrays.asList(args));

		if (page != null) {
			StringBuilder total = new StringBuilder("SELECT count(*) FROM ");
			total.append(sql.toString().toUpperCase().trim().replaceAll("^SELECT.*?\\sFROM\\s", ""));
			int iTotal = queryCount(total.toString(), params);
			page.setTotalCount(iTotal);

			sql.append(" LIMIT ?,?;");
			params.add(Integer.valueOf(page.getStart()));
			params.add(Integer.valueOf(page.getPageSize()));
		}

		instanceList = executeQueryList(sql.toString(), params, t);
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql.toString() + Arrays.toString(args));
		}
		if (t.needCached()) {
			entityCache.putEntityList(t.getClass(), Arrays.toString(keys), args, instanceList, page);
		}
		return instanceList;
	}

	@Override
	public List<Integer> getEntityIdList(String sql, Object[] args) throws Exception {
		if (LOGENABLE) {
			log.info("Generic SQL [" + sql + Arrays.toString(args));
		}
		return executeQueryIdList(sql, Arrays.asList(args));
	}

	@Override
	public List<Integer> getEntityIdList(String sql, Object[] args, Page page) throws Exception {
		List<Object> params = new LinkedList<Object>();
		params.addAll(Arrays.asList(args));
		StringBuilder tmp = new StringBuilder(sql.trim().replaceAll(";$", ""));

		if (page != null) {
			StringBuilder total = new StringBuilder("SELECT count(1) FROM (");
			total.append(sql.trim().replaceAll(";$", "")).append(") AS TMP").append(System.currentTimeMillis());
			page.setTotalCount(queryCount(total.toString(), params));

			tmp.append(" LIMIT ?,?;");
			params.add(Integer.valueOf((page.getCurrentPage() - 1) * page.getPageSize()));
			params.add(Integer.valueOf(page.getPageSize()));
		}

		if (LOGENABLE) {
			log.info("Generic SQL [" + tmp.toString() + Arrays.toString(args));
		}
		return executeQueryIdList(tmp.toString(), params);
	}

	@Override
	public List<JSONObject> search(String sql, Object[] args, Page page) throws Exception {
		List<Object> params = new LinkedList<Object>();
		params.addAll(Arrays.asList(args));
		StringBuilder tmp = new StringBuilder(sql.trim().replaceAll(";$", ""));

		if (page != null) {
			//String total = new StringBuilder("SELECT count(*) FROM (").append(sql).append(") tempTable").toString();
			StringBuilder total = new StringBuilder("SELECT count(1) FROM (");
			total.append(sql.toUpperCase().trim().replaceAll(";$", "")
					.replaceAll("^SELECT.*?\\sFROM\\s", "SELECT 1 FROM "));
			total.append(") tempTable");
			page.setTotalCount(queryCount(total.toString(), params));

			tmp.append(" LIMIT ?,?;");
			params.add(Integer.valueOf((page.getCurrentPage() - 1) * page.getPageSize()));
			params.add(Integer.valueOf(page.getPageSize()));
		}

		if (LOGENABLE) {
			log.info("Generic SQL [" + tmp.toString() + Arrays.toString(args));
		}
		return executeSearch(tmp.toString(), params);
	}

	@Override
	public int execute(String sql, Object[] args, T t) {
		int res = execute(sql, Arrays.asList(args));
		if (t.needCached()) {
			try {
				entityCache.removeType(t.getClass());
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return res;
	}

	/**
	 */
	private JSONObject parseEntiryAndColumn(T sobj) throws Exception {
		JSONObject jobj = new JSONObject();
		jobj.put("entity", sobj.getEntity());

		String columnName, value;
		VariantType variantType;
		int length;

		jobj.put("column", new JSONArray());
		for (String key : sobj.keySet()) {
			if (!sobj.isPersistent(key)) {
				continue;
			}
			columnName = sobj.getAliasName(key);
			if (columnName == null) {
				columnName = key;
			}

			value = sobj.getString(key);
			if (!sobj.isNullable(key) && value == null) {
				throw new Exception(columnName + " should not be null.");
			}

			length = sobj.getLength(key);
			if (length > 0 && value != null && length < value.toString().length()) {
				throw new Exception("The value of '" + columnName + "' exceed max length.");
			}

			variantType = sobj.getVariantType(key);
			if (value != null) {
				switch (variantType) {
				case BOOLEAN:
					try {
						Boolean.parseBoolean(value.toString());
					} catch (Exception e) {
						throw new Exception("The value of '" + columnName + "' should be datetime.");
					}
					break;
				case DATETIME:
					try {
						new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(value.toString());
					} catch (Exception e) {
						throw new Exception("The value of '" + columnName + "' should be datetime.");
					}
					break;
				case DOUBLE:
					try {
						Double.parseDouble(value.toString());
					} catch (Exception e) {
						throw new Exception("The value of '" + columnName + "' should be double.");
					}
					break;
				case INTEGER:
					try {
						Long.parseLong(value.toString());
					} catch (Exception e) {
						throw new Exception("The value of '" + columnName + "' should be integer.");
					}
					break;
				case STRING:
					break;
				default:
					break;
				}

				if (value != null) {
					jobj.getJSONArray("column").put(
							new JSONObject().put("name", columnName).put("value", value)
									.put("pk", (sobj.isPk(key) ? true : false)));
				}
			}
		}
		return jobj;
	}

	private int[] executeBatch(final String sql, final List<List<Object>> batchParamList, final boolean inTrans) {
		return this.getJdbcTemplate().execute(new ConnectionCallback<int[]>() {
			@Override
			public int[] doInConnection(Connection conn) throws SQLException, DataAccessException {
				int[] res;
				try {
					if (!inTrans) {
						System.out.println("isTransaction");
						conn.setAutoCommit(false);
					}
					PreparedStatement pstmt = conn.prepareStatement(sql);
					for (List<Object> paramList : batchParamList) {
						boxParameters(pstmt, paramList);
						pstmt.addBatch();
					}
					res = pstmt.executeBatch();
					if (!inTrans) {
						conn.commit();
					}
				} catch (SQLException e) {
					log.error("execute Error sql: [ " + sql + " ]");
					throw e;
				}
				return res;
			}
		});
	}

	private int execute(final String sql, final List<Object> paramList) {
		return this.getJdbcTemplate().execute(new ConnectionCallback<Integer>() {
			@Override
			public Integer doInConnection(Connection conn) throws SQLException, DataAccessException {
				int res = 0;
				try {
					PreparedStatement pstmt = conn.prepareStatement(sql);
					boxParameters(pstmt, paramList);
					res = pstmt.executeUpdate();
				} catch (SQLException e) {
					log.error("execute Error sql: [ " + sql + " ]");
					throw e;
				}
				return res;
			}
		});
	}

	private T executeQuery(final String sql, final List<Object> paramList, final T obj) {

		return this.getJdbcTemplate().execute(new ConnectionCallback<T>() {

			@Override
			public T doInConnection(Connection conn) throws SQLException, DataAccessException {
				try {
					PreparedStatement pstmt = conn.prepareStatement(sql);
					boxParameters(pstmt, paramList);
					ResultSet rs = pstmt.executeQuery();
					while (rs.next()) {
						return boxInstance(rs, obj);
					}
				} catch (SQLException e) {
					log.error("executeQuery Error sql: [ " + sql + " ]");
					throw e;
				}
				return null;
			}
		});
	}

	private List<T> executeQueryList(final String sql, final List<Object> paramList, final T t) {
		return this.getJdbcTemplate().execute(new ConnectionCallback<List<T>>() {

			@Override
			public List<T> doInConnection(Connection conn) throws SQLException, DataAccessException {
				List<T> tList = new LinkedList<T>();
				try {
					PreparedStatement pstmt = conn.prepareStatement(sql);
					boxParameters(pstmt, paramList);
					ResultSet rs = pstmt.executeQuery();
					while (rs.next()) {
						tList.add(boxInstance(rs, t));
					}
				} catch (SQLException e) {
					log.error("executeQueryList Error sql: [ " + sql + " ]");
					throw e;
				}
				return tList;
			}
		});
	}

	@SuppressWarnings("unchecked")
	private T boxInstance(ResultSet rs, T obj) {
		String columnName = null;
		T instance = null;
		try {
			instance = (T) obj.getClass().newInstance();
			for (String key : obj.keySet()) {
				if (!obj.isPersistent(key)) {
					continue;
				}
				columnName = obj.getAliasName(key);
				if (columnName == null) {
					columnName = key;
				}
				String value = rs.getString(columnName);
				if (value != null) {
					instance.set(key, value);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return instance;
	}

	private List<Integer> executeQueryIdList(final String sql, final List<Object> paramList) {
		return this.getJdbcTemplate().execute(new ConnectionCallback<List<Integer>>() {
			@Override
			public List<Integer> doInConnection(Connection conn) throws SQLException, DataAccessException {
				PreparedStatement pstmt = conn.prepareStatement(sql);
				boxParameters(pstmt, paramList);
				ResultSet rs = pstmt.executeQuery();
				List<Integer> tList = new LinkedList<Integer>();
				while (rs.next()) {
					tList.add((Integer) rs.getInt("id"));
				}
				return tList;
			}
		});
	}

	private List<JSONObject> executeSearch(final String sql, final List<Object> paramList) {
		return this.getJdbcTemplate().execute(new ConnectionCallback<List<JSONObject>>() {

			@Override
			public List<JSONObject> doInConnection(Connection conn) throws SQLException, DataAccessException {
				PreparedStatement pstmt = conn.prepareStatement(sql);
				boxParameters(pstmt, paramList);
				ResultSet rs = null;
				try {
					rs = pstmt.executeQuery();
				} catch (SQLException e1) {
					log.error("executeSearch Error sql: [ " + sql + " ]");
					throw e1;
				}
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				List<JSONObject> jrsList = new LinkedList<JSONObject>();
				JSONObject jobj = null;
				try {
					while (rs.next()) {
						jobj = new JSONObject();
						for (int i = 1; i <= columnCount; i++) {
							jobj.put(rsmd.getColumnLabel(i), rs.getObject(i));
						}
						jrsList.add(jobj);
					}
				} catch (JSONException e) {
					e.printStackTrace();
				}
				return jrsList;
			}
		});
	}

	private int queryCount(final String sql, final List<Object> params) {
		return this.getJdbcTemplate().execute(new ConnectionCallback<Integer>() {
			@Override
			public Integer doInConnection(Connection conn) throws SQLException, DataAccessException {
				int count = 0;
				try {
					PreparedStatement pstmt = conn.prepareStatement(sql);
					boxParameters(pstmt, params);
					ResultSet rs = pstmt.executeQuery();
					if (rs.next()) {
						count = rs.getInt(1);
					}
				} catch (SQLException e) {
					log.error("queryCount Error sql: [ " + sql + " ]");
					throw e;
				}
				return count;
			}
		});
	}

	private void boxParameters(PreparedStatement pstmt, List<Object> paramList) throws SQLException {
		for (int i = 1; i <= paramList.size(); i++) {
			pstmt.setObject(i, paramList.get(i - 1));
		}
	}

	private synchronized void generatePK(T obj) throws Exception {
		String tableName = obj.getEntity();

		for (String key : obj.keySet()) {
			if (!obj.isPk(key)) {
				continue;
			}
			if (obj.getInt(key) != 0) {
				continue;
			}
			obj.set(key, idsDAO.generateId(tableName));
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> getEntityList(Map<String, JSONObject> map, T t, Page page, String orderBy) throws Exception {
		StringBuilder sqlTable = new StringBuilder("select x.* from ");
		String sqlWhere = " where 1=1 ";
		StringBuilder sqlParam = new StringBuilder();
		String value;
		String param;
		JSONObject json;
		Iterator<String> iterator;

		for (String table : map.keySet()) {
			json = map.get(table);
			sqlTable.append(table).append(" ,");
			iterator = json.keys();
			while (iterator.hasNext()) {
				param = iterator.next();
				value = json.get(param).toString();
				sqlParam.append(" AND ").append(param).append(value);
			}
		}
		String sql = sqlTable.substring(0, sqlTable.length() - 1) + sqlWhere + sqlParam.toString();
		sql += orderBy;
		return this.getEntityList(sql, new Object[] {}, t, page);
	}
}
